package dastsource;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import javax.sql.*;
import java.sql.*;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.commons.dbutils.*;

/**
 * 数据库连接池及现成的数据库处理包
 *
 * @author DuJiabao
 * @create 2021-01-16 16:41
 */
public class DruidTest {
    // 根据properties获取数据库连接池，供所有方法使用
    private static DataSource dataSource = null;

    static {
        Properties config = new Properties();
        try {
            config.load(ClassLoader.getSystemClassLoader().getResourceAsStream("dastsource/config.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(config);  // 创建数据库连接池
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    @Test
    public void test() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = dataSource.getConnection(); // 通过数据库连接池获取一个连接
            String sql = "update new_table2 set email=? where employee_id=?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1, "666666@qq.com");
            ps.setObject(2, 1001);
            System.out.println(ps.executeUpdate());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeQuietly(conn);  // 归还连接，但实际上并没有关闭物理连接。
            DbUtils.closeQuietly(ps);
        }
    }

    @Test
    public void queryRunnerInsertTest() {
        Connection conn = null;
        // 如果要内部帮你新建事务，需要传入dataSource，否则不需要
        QueryRunner queryRunner = new QueryRunner();// 实例化Runner
        //QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql = "insert into new_table3 values(?,?);";
        try {
            conn = dataSource.getConnection();
            int insert = queryRunner.update(conn, sql, "jack", 1000);  // 非单个事务，将conn放进去，否则会帮你创建一个conn
            System.out.println(insert);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DbUtils.closeQuietly(conn);
        }
    }

    @Test
    public void queryRunnerQueryTest() {
        Connection conn = null;
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select last_name lastName, balance from new_table3 where last_name=?;";

        try {
            conn = dataSource.getConnection();
            BeanHandler<Employee> employeeBeanHandler = new BeanHandler<>(Employee.class);  // 以对象的方式保存查询结果
            Employee query = queryRunner.query(conn, sql, employeeBeanHandler, "jack");
            System.out.println(query);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DbUtils.closeQuietly(conn);
        }
    }

    @Test
    public void queryRunnerQuery2Test() {
        Connection conn = null;
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select last_name lastName, balance from new_table3;";
        try {
            conn = dataSource.getConnection();
            BeanListHandler<Employee> employeeBeanListHandler = new BeanListHandler<>(Employee.class);
            List<Employee> query = queryRunner.query(conn, sql, employeeBeanListHandler);
            query.forEach(System.out::println);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DbUtils.closeQuietly(conn);
        }
    }

    @Test
    public void queryRunnerQuery3Test() {
        Connection conn = null;
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select last_name lastName, balance from new_table3;";
        try {
            conn = dataSource.getConnection();
            MapListHandler mapListHandler = new MapListHandler();  // 以Map的方式保存查询结果
            List<Map<String, Object>> query = queryRunner.query(conn, sql, mapListHandler);
            query.forEach(System.out::println);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DbUtils.closeQuietly(conn);
        }
    }

    @Test
    public void queryRunnerQuery4Test() {
        Connection conn = null;
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select count(*) from new_table3;";
        try {
            conn = dataSource.getConnection();
            ScalarHandler scalarHandler = new ScalarHandler();// 用于查特殊值
            Long query = (Long) queryRunner.query(conn, sql, scalarHandler);
            System.out.println(query);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DbUtils.closeQuietly(conn);
        }
    }

    @Test
    public void queryRunnerQuery5Test() {
        Connection conn = null;
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select count(*) from new_table3;";
        try {
            conn = dataSource.getConnection();
            // 匿名类
            ResultSetHandler<Employee> handler = new ResultSetHandler<Employee>() {
                @Override
                public Employee handle(ResultSet resultSet) throws SQLException {
                    return null;
                }
            };
            Employee query = queryRunner.query(conn, sql, handler);
            System.out.println(query);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DbUtils.closeQuietly(conn);
        }
    }
}
